﻿from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (
    Column, Integer, String, Unicode, LargeBinary,
    UnicodeText, Boolean, DateTime, Float
)
from sqlalchemy import Sequence, Index
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )
from zope.sqlalchemy import ZopeTransactionExtension

import datetime

engine = create_engine('mysql://cvm:cvm123@210.209.70.134/cvm?charset=utf8', echo=True)
# engine = create_engine('oracle+cx_oracle://cvm:cvm123@210.209.70.134:1521/orcl', echo=True)

Base = declarative_base()

# Add By Mars.Yan 2017-01-05
class CvmAge(Base): # 年龄段字典表
    __tablename__ = 'cvm_age'
    id = Column(Integer, Sequence('seq_cvmage_id'), primary_key=True)
    age = Column(Unicode(16), nullable=False)   # 年龄段名称
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmCareer(Base):  # 事业字典表
    __tablename__ = 'cvm_career'
    id = Column(Integer, Sequence('seq_cvmcareer_id'), primary_key=True)
    career = Column(Unicode(255), nullable=False)   # 事业名称
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmCartype(Base): # 车辆类型表
    __tablename__ = 'cvm_cartype'
    id = Column(Integer, Sequence('seq_cvmcartype_id'), primary_key=True)
    cartype = Column(Unicode(128), nullable=False)  # 车辆类型
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmColor(Base):   # 颜色码表
    __tablename__ = 'cvm_color'
    id = Column(Integer, Sequence('seq_cvmcolor_id'), primary_key=True)
    color = Column(Unicode(32), nullable=False)         # 颜色中文名称
    colorvalue = Column(Unicode(16), nullable=False)    # 颜色值
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmCustom(Base):  # 潜客表
    __tablename__ = 'cvm_custom'
    id = Column(Integer, Sequence('seq_cvmcustom_id'), primary_key=True)
    customid = Column(Unicode(64), nullable=False)  # 潜客id
    sex = Column(Unicode(4), nullable=False)        # 性别
    age = Column(Unicode(16), nullable=False)       # 年龄段
    year = Column(Unicode(16), nullable=False)      # 年代
    province = Column(Unicode(32), nullable=False)  # 省份
    city = Column(Unicode(32), nullable=False)      # 城市
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmCx(Base):  # 车系表
    __tablename__ = 'cvm_cx'
    id = Column(Integer, Sequence('seq_cvmcx_id'), primary_key=True)
    cs = Column(Unicode(64), nullable=False)    # 厂商
    pp = Column(Unicode(32), nullable=False)    # 品牌
    cx = Column(Unicode(64), nullable=False)    # 车系
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmEducation(Base):   # 学历字典表
    __tablename__ = 'cvm_education'
    id = Column(Integer, Sequence('seq_cvmeducation_id'), primary_key=True)
    education = Column(Unicode(64), nullable=False) # 学历名称
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmFamilynum(Base):   # 家庭人口数字典表
    __tablename__ = 'cvm_familynum'
    id = Column(Integer, Sequence('seq_cvmfamilynum_id'), primary_key=True)
    familynum = Column(Unicode(16), nullable=False) # 家庭人口数
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmFamilytype(Base):  # 家庭类型字典表
    __tablename__ = 'cvm_familytype'
    id = Column(Integer, Sequence('seq_cvmfamilytype_id'), primary_key=True)
    familytype = Column(Unicode(32), nullable=False) # 家庭类型名称
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmHc(Base):  # 住房情况字典表
    __tablename__ = 'cvm_hc'
    id = Column(Integer, Sequence('seq_cvmhc_id'), primary_key=True)
    hc = Column(Unicode(16), nullable=False) # 住房情况名称
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmQsbg(Base):    # 权属变更表
    __tablename__ = 'cvm_qsbg'
    id = Column(Integer, Sequence('seq_cvmqsbg_id'), primary_key=True)
    hp = Column(Unicode(32), nullable=False)        # 号牌
    clsbdh = Column(Unicode(32), nullable=False)    # 车辆识别代号
    hpzl = Column(Unicode(16), nullable=False)      # 号牌种类
    yhphm = Column(Unicode(16), nullable=False)     # 原号牌号码
    zyqdz = Column(Unicode(255), nullable=False)    # 转移前地址
    bgrq = Column(DateTime, nullable=False)         # 变更日期
    yjdcsyr = Column(Unicode(16), nullable=False)   # 原机动车所有人
    ysyxz = Column(Unicode(16), nullable=False)     # 原使用性质
    pzjg = Column(Unicode(16), nullable=False)      # 批注机关
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmQwclq(Base):   # 潜客浏览表
    __tablename__ = 'cvm_qwclq'
    id = Column(Integer, Sequence('seq_cvmqwclq_id'), primary_key=True)
    customid = Column(Unicode(128), nullable=False)  # 潜客id
    webid = Column(Integer, nullable=False)     # 网址id
    cxid = Column(Integer, nullable=False)      # 车系id
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmSalary(Base):  # 年薪字典表
    __tablename__ = 'cvm_salary'
    id = Column(Integer, Sequence('seq_cvmsalary_id'), primary_key=True)
    salary = Column(Unicode(16), nullable=False)    # 年薪名称
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmSex(Base):  # 性别字典表
    __tablename__ = 'cvm_sex'
    id = Column(Integer, Sequence('seq_cvmsex_id'), primary_key=True)
    sex = Column(Unicode(8), nullable=False)    # 性别
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmSkodacustom(Base): # 斯柯达潜客自然属性分布表
    __tablename__ = 'cvm_skodacustom'
    id = Column(Integer, Sequence('seq_cvmskodacustom_id'), primary_key=True)
    customid = Column(Unicode(128), nullable=False)     # 主键id
    sex = Column(Integer, nullable=False)            # 性别
    age = Column(Integer, nullable=False)            # 年龄段
    year = Column(Unicode(255), nullable=False)         # 年代
    province = Column(Unicode(255), nullable=False)     # 省份
    city = Column(Unicode(255), nullable=False)         # 城市
    career = Column(Unicode(255), nullable=False)       # 职业
    salary = Column(Integer, nullable=False)        # 薪资收入
    familynum = Column(Integer, nullable=False)     # 家庭人口数
    familytype = Column(Unicode(255), nullable=False)   # 家庭类型
    zfqk = Column(Unicode(255), nullable=False)         # 住房情况
    education = Column(Unicode(255), nullable=False)    # 学历
    lifestage = Column(Unicode(255), nullable=False)    # 人生阶段
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmWeb(Base): # 网站表
    __tablename__ = 'cvm_web'
    id = Column(Integer, Sequence('seq_cvmweb_id'), primary_key=True)
    webname = Column(Unicode(32), nullable=False)       # 网站名称
    url = Column(Unicode(32), nullable=False)           # 网站网址
    description = Column(UnicodeText)                   # 网站描述
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)



class CvmXkxx(Base): # 现客信息表
    __tablename__ = 'cvm_xkxx'
    id = Column(Integer, Sequence('seq_cvmweb_id'), primary_key=True)
    hp = Column(Unicode(32), nullable=False)        # 号牌
    vin = Column(Unicode(32), nullable=False)       # vin码
    zwpp = Column(Unicode(16), nullable=False)      # 中文品牌
    clxh = Column(Unicode(32), nullable=False)      # 车辆型号
    cllx = Column(Integer, nullable=False)      # 车辆类型
    clsbdh = Column(Unicode(32), nullable=False)    # 车辆识别代号
    fdjh = Column(Unicode(16), nullable=False)      # 发动机号
    csys = Column(Unicode(64), nullable=False)      # 车身颜色
    syxz = Column(Unicode(16), nullable=False)      # 使用性质
    ccrq = Column(DateTime, nullable=False)         # 出厂日期
    qzbfqz = Column(DateTime, nullable=False)       # 强制报废期止
    jyyxqz = Column(DateTime, nullable=False)       # 检验有效期止
    bxzzrq = Column(DateTime, nullable=False)       # 保险终止日期
    jdczt = Column(Unicode(16), nullable=False)     # 机动车状态
    jdcsyr = Column(Unicode(64), nullable=False)    # 机动车所有人
    ccdjrq = Column(DateTime, nullable=False)       # 初登记日期
    djzs = Column(Unicode(255), nullable=False)     # 登记住所
    lxdh = Column(Unicode(64), nullable=False)      # 联系电话
    gcjk = Column(Unicode(8), nullable=False)       # 国产/进口
    sgty = Column(Unicode(32), nullable=False)      # 事故逃逸
    tpc = Column(Unicode(32), nullable=False)       # 套牌车
    dqc = Column(Unicode(32), nullable=False)       # 盗抢车
    hgbzbh = Column(Unicode(64), nullable=False)    # 合格标志编号
    xszxbh = Column(Unicode(64), nullable=False)    # 行驶证芯编号
    hdzzl = Column(Unicode(32), nullable=False)     # 核定载质量
    hdzk = Column(Integer, nullable=False)      # 核定载客
    jsszk = Column(Unicode(16), nullable=False)     # 驾驶室载客
    zzl = Column(Integer, nullable=False)       # 总质量
    zbzz = Column(Integer, nullable=False)      # 装备质量
    zqyzzl = Column(Integer, nullable=False)    # 准牵引总质量
    gbthps = Column(Integer, nullable=False)    # 钢板弹簧片数
    pl = Column(Integer, nullable=False)        # 排量
    gl = Column(Integer, nullable=False)        # 功率
    hbdb = Column(Unicode(64), nullable=False)      # 环保达标
    wkcc = Column(Unicode(128), nullable=False)     # 外廓尺寸
    hxnbcc = Column(Unicode(128), nullable=False)   # 货箱内部尺寸
    cjdw = Column(Unicode(255), nullable=False)     # 承检单位
    bxgs = Column(Unicode(255), nullable=False)     # 保险公司
    djjg = Column(Unicode(32), nullable=False)      # 登记机关
    sfzmhm = Column(Unicode(64), nullable=False)    # 身份证明号码
    zsxzqh = Column(Unicode(32), nullable=False)    # 住所行政区划
    yzbm = Column(Integer, nullable=False)      # 邮政编码
    dybj = Column(Unicode(16), nullable=False)      # 抵押标记
    zyjl = Column(Integer, nullable=False)      # 转移记录
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmGccyc(Base): # 国产乘用车表(2015年)
    __tablename__ = 'cvm_gccyc_2015'
    id = Column(Integer, Sequence('seq_cvmweb_id'), primary_key=True)
    datamonth = Column(DateTime, nullable=False)            # 数据来源月份
    province = Column(Unicode(16), nullable=False)          # 省份
    city = Column(Unicode(16), nullable=False)              # 城市
    town = Column(Unicode(16), nullable=False)              # 区/县
    brand = Column(Unicode(16), nullable=False)             # 厂商
    vehicle_model = Column(Unicode(32), nullable=False)     # 车型
    vehicle_series = Column(Unicode(32), nullable=False)    # 车系
    vehicle_name = Column(Unicode(16), nullable=False)      # 车辆名称
    vehicle_body = Column(Unicode(8), nullable=False)       # 车身结构
    color = Column(Unicode(8), nullable=False)              # 颜色
    displacement = Column(Unicode(8), nullable=False)       # 排量
    domestic = Column(Unicode(8), nullable=False)           # 进口/国产
    country = Column(Unicode(32), nullable=False)           # 原产国
    manufacturer = Column(Unicode(16), nullable=False)      # 制造商
    birthyear = Column(Integer, nullable=False)             # 车主出生年份
    gender = Column(Unicode(8), nullable=False)             # 性别
    usefor = Column(Unicode(8), nullable=False)             # 车辆使用性质
    ownership = Column(Unicode(16), nullable=False)         # 车辆所有人
    paytype = Column(Unicode(8), nullable=False)            # 付款方式
    number = Column(Integer, nullable=False)                # number
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmJkcyc(Base): # 进口乘用车表
    __tablename__ = 'cvm_jkcyc_2015'
    id = Column(Integer, Sequence('seq_cvmweb_id'), primary_key=True)
    datamonth = Column(DateTime, nullable=False)            # 数据来源月份
    province = Column(Unicode(16), nullable=False)          # 省份
    city = Column(Unicode(16), nullable=False)              # 城市
    town = Column(Unicode(16), nullable=False)              # 区/县
    brand = Column(Unicode(16), nullable=False)             # 厂商
    vehicle_model = Column(Unicode(32), nullable=False)     # 车型
    vehicle_series = Column(Unicode(32), nullable=False)    # 车系
    vehicle_name = Column(Unicode(16), nullable=False)      # 车辆名称
    vehicle_body = Column(Unicode(8), nullable=False)       # 车身结构
    color = Column(Unicode(8), nullable=False)              # 颜色
    displacement = Column(Unicode(8), nullable=False)       # 排量
    domestic = Column(Unicode(8), nullable=False)           # 进口/国产
    country = Column(Unicode(32), nullable=False)           # 原产国
    manufacturer = Column(Unicode(16), nullable=False)      # 制造商
    birthyear = Column(Integer, nullable=False)             # 车主出生年份
    gender = Column(Unicode(8), nullable=False)             # 性别
    usefor = Column(Unicode(8), nullable=False)             # 车辆使用性质
    ownership = Column(Unicode(16), nullable=False)         # 车辆所有人
    paytype = Column(Unicode(8), nullable=False)            # 付款方式
    number = Column(Integer, nullable=False)                # number
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


# class CvmSq4S(Base):    # 上汽4S店
#     __tablename__ = 'cvm_sq4S'
#     id = Column(Integer, Sequence('seq_cvmsq4s_id'), primary_key=True)
#     pro = Column(Unicode(16), nullable=False)               # 省/市
#     regionname = Column(Unicode(16), nullable=False)        # 地区
#     shopname = Column(Unicode(32), nullable=False)          # 店名简称
#     shopfullname = Column(Unicode(32), nullable=False)      # 店名全称
#     address = Column(Unicode(256), nullable=False)          # 地址
#     postcode = Column(Unicode(16), nullable=False)          # 邮编
#     tel = Column(Unicode(128), nullable=False)              # 电话
#     shoptype = Column(Unicode(8), nullable=False)           # 店铺类型
#     mainbrands = Column(Unicode(256), nullable=False)       # 主营品牌
#     saleregion = Column(Unicode(16), nullable=False)        # 销售范围
#     cityname = Column(Unicode(16), nullable=False)          # 城市名（预留）
#     longitude = Column(Float, nullable=False)               # 经度
#     latitude = Column(Float, nullable=False)                # 维度
#     shopnum = Column(Unicode(32), nullable=False)           #
#     shopcode = Column(Unicode(32), nullable=False)          #
#     shopasc = Column(Unicode(32), nullable=False)           #
#     dearshortname = Column(Unicode(32), nullable=False)     #
#     state = Column(Unicode(32), nullable=False)             # 4S店状态
#     procode = Column(Unicode(32), nullable=False)           #
#     stcity = Column(Unicode(32), nullable=False)            #
#     county = Column(Unicode(32), nullable=False)            #
#     countycode = Column(Unicode(32), nullable=False)        #
#     tier = Column(Unicode(32), nullable=False)              #
#     location = Column(Unicode(256), nullable=False)         # 位置
#     belong = Column(Unicode(256), nullable=False)           # 所属集团
#     created = Column(DateTime, default=datetime.datetime.now, nullable=False)
#     timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)

class Cvm4SShop(Base):    # 4S店
    __tablename__ = 'cvm_4s_shop'
    id = Column(Integer, Sequence('seq_cvmsq4s_id'), primary_key=True)
    pro = Column(Unicode(16), nullable=True)               # 省/市
    regionname = Column(Unicode(16), nullable=True)        # 地区
    shopfullname = Column(Unicode(32), nullable=True)      # 店名全称
    shopname = Column(Unicode(32), nullable=False)          # 店名简称
    shoptype = Column(Unicode(8), nullable=True)           # 店铺类型
    location = Column(Unicode(256), nullable=True)         # 位置
    mainbrands = Column(Unicode(256), nullable=True)       # 主营品牌
    saleregion = Column(Unicode(16), nullable=True)        # 销售范围
    cityname = Column(Unicode(16), nullable=True)          # 城市名
    weidu = Column(Unicode(32), nullable=True)             # 经纬度
    longitude = Column(Unicode(16), nullable=True)         # 经度
    latitude = Column(Unicode(16), nullable=True)          # 维度
    shopnum = Column(Unicode(32), nullable=True)           #
    shopcode = Column(Unicode(32), nullable=True)          #
    shopasc = Column(Unicode(32), nullable=True)           #
    dearshortname = Column(Unicode(32), nullable=True)     #
    type = Column(Unicode(16), nullable=True)              #
    state = Column(Unicode(32), nullable=True)             # 4S店状态
    procode = Column(Unicode(32), nullable=True)           #
    stcity = Column(Unicode(32), nullable=True)            #
    county = Column(Unicode(32), nullable=True)            #
    countycode = Column(Unicode(32), nullable=True)        #
    tier = Column(Unicode(32), nullable=True)              #
    address = Column(Unicode(256), nullable=True)          # 地址
    longdegree = Column(Unicode(16), nullable=True)        # 经度（度）
    longminute = Column(Unicode(16), nullable=True)        # 经度（分）
    longsec = Column(Unicode(16), nullable=True)           # 经度（秒）
    latdegree = Column(Unicode(16), nullable=True)         # 经度（度）
    latminute = Column(Unicode(16), nullable=True)         # 经度（分）
    latsec = Column(Unicode(16), nullable=True)            # 经度（秒）
    postcode = Column(Unicode(16), nullable=True)          # 邮编
    opyear = Column(Unicode(4), nullable=True)             #
    opmonth = Column(Unicode(2), nullable=True)            #
    opday = Column(Unicode(2), nullable=True)              #
    opdate = Column(Unicode(16), nullable=True)            #
    closeyear = Column(Unicode(4), nullable=True)          #
    closemonth = Column(Unicode(2), nullable=True)         #
    closeday= Column(Unicode(2), nullable=True)            #
    closedate = Column(Unicode(16), nullable=True)         #
    tel = Column(Unicode(128), nullable=True)              # 电话
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


class CvmAreaInfo(Base):    # 地域信息表
    __tablename__ = 'cvm_areainfo'
    id = Column(Integer, Sequence('seq_cvmpoop_id'), primary_key=True)
    popyear = Column(Integer, nullable=False)            # 统计年份
    province = Column(Unicode(16), nullable=False)       # 省/市
    area = Column(Float, nullable=False)                 # 面积
    population = Column(Integer, nullable=False)         # 人口
    created = Column(DateTime, default=datetime.datetime.now, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=False)


# End By Yanshan

def __repr__(self):
    return "<User(name='%s', fullname='%s', password='%s')>" % \
           (self.name, self.fullname, self.password)


def init_db():
    Base.metadata.create_all(engine)


def add_user(newUser):
    session = Session()
    session.add(newUser)
    session.commit()


if __name__ == '__main__':
    Session = sessionmaker(bind=engine)
    init_db()
    # ed_user = Users(name='ed', fullname='Ed Jones', password='edspassword')
    # add_user(ed_user)
